package com.dragons.dragonsvf.controller.amin_utils;

import com.dragons.dragonsvf.config.DynamicMinioConfig;
//import com.dragons.dragonsvf.config.MinioConfig;
import com.dragons.dragonsvf.entities.ResponseDTO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @author dragons
 * @version 1.0.0
 * @description: 检查两个 excel 文件所有单元格的值是否相同，并且生成合并后的EXCEL和标注的不同
 * @date 2025/4/22 10:36
 */
@RestController
public class ChkTwoExcelAllCellDiff {

//    @Autowired
//    MinioConfig minioConfig;

    @Autowired
    DynamicMinioConfig minioConfig;



    /**
     * 对比两个文件
     * @param fileFirst  第一个Excel文件
     * @param fileSecond 第二个Excel文件
     * @param formData   添加的额外数据
     * @return 放回方式修改为 HttpServletResponse response
     * @throws Exception
     */
    @PostMapping(value = "/chkExcelAllCell", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public ResponseDTO chkExcelAllCell(
        @RequestParam("excel_first") MultipartFile fileFirst,
        @RequestParam("excel_second") MultipartFile fileSecond,
        @RequestParam(required = false) Map<String, String> formData
    ) throws Exception {
        // 读取两个 Excel 文件
        Workbook workbook1 = WorkbookFactory.create(fileFirst.getInputStream());
        Workbook workbook2 = WorkbookFactory.create(fileSecond.getInputStream());

        // 创建新的工作簿
        Workbook mergedWorkbook = new XSSFWorkbook();
        Sheet newSheet = mergedWorkbook.createSheet("合并Sheet");

        // 获取两个文件的第一个 Sheet
        Sheet sheet1 = workbook1.getSheetAt(0);
        Sheet sheet2 = workbook2.getSheetAt(0);

        // 合并数据
        int maxRows = Math.max(sheet1.getLastRowNum(), sheet2.getLastRowNum());
        for (int i = 0; i <= maxRows; i++) {
            Row newRow = newSheet.createRow(i);
            Row row1 = sheet1.getRow(i);
            Row row2 = sheet2.getRow(i);

            boolean hasDataInRow1 = row1 != null;
            boolean hasDataInRow2 = row2 != null;

            if (hasDataInRow1) {
                int cellIndex = 0;
                for (Cell cell : row1) {
                    Cell newCell = newRow.createCell(cellIndex);
                    copyCellValueTwo(cell, newCell);
                    cellIndex++;
                }
            }

            // 只有当至少一行有数据时，才填充中间列
            if (hasDataInRow1 || hasDataInRow2) {
                int emptyCellIndex = (row1 != null)? row1.getLastCellNum() : 0;
                Cell middleCell = newRow.createCell(emptyCellIndex);
                middleCell.setCellValue("@@@@");
            }

            if (hasDataInRow2) {
                int startCellIndex = (row1 != null)? row1.getLastCellNum() + 1 : 0;
                for (Cell cell : row2) {
                    Cell newCell = newRow.createCell(startCellIndex);
                    copyCellValueTwo(cell, newCell);
                    startCellIndex++;
                }
            }
        }

        // 最终结果集
        Map<List<String>,List<String>> collectValues = new HashMap<>();

        // 将表格中的数据转换为 String 二维数组 -----------------------------------------------------------------------
        Sheet sheetArr = mergedWorkbook.getSheetAt(0);
        int rowCount = sheetArr.getLastRowNum() + 1; // getLastRowNum()返回最后一行索引，从0开始，+1得到总行数
        String[][] dataArray = new String[rowCount][];
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            Row row = sheetArr.getRow(rowNum);
            int lastCellNum = (row != null) ? row.getLastCellNum() : -1; // 获取该行最后一个单元格索引（无单元格时为-1）
            int columnCount = (lastCellNum >= 0) ? lastCellNum + 1 : 0; // 列数=最后索引+1，无单元格时为0
            String[] cellArray = new String[columnCount];
            for (int cellNum = 0; cellNum < columnCount; cellNum++) {
                Cell cell = (row != null) ? row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK) : null;
                String cellValue = "";
                if (cell != null) {
                    cell.setCellType(CellType.STRING); // 统一转换为字符串类型
                    cellValue = cell.getStringCellValue();
                }
                cellArray[cellNum] = cellValue;
            }
            dataArray[rowNum] = cellArray;
        }

        // 开始对比
        for (String[] strings : dataArray) {
            List<String> sl = Arrays.asList(strings);
            int idx = sl.indexOf("@@@@");

            // 获取两个文件当前行的值数组方便后面对比操作
            String[] arr1 = Arrays.copyOfRange(strings, 0, idx);
            String[] arr2 = Arrays.copyOfRange(strings, idx + 1, strings.length);

            /* value 断点条件
            arr1[1].equals("E.R.T.0000405") == true || arr1[1].equals("E.I.O.0000887") == true || arr1[1].equals("E.I.P.0000369") == true ||
            arr1[1].equals("E.N.P.0000132") == true || arr1[1].equals("E.R.T.0000669") == true || arr1[1].equals("E.R.T.0001008") == true || arr1[1].equals("E.R.T.0001099") == true
            */

            // 遍历对比两个文件当前行的值
            StringBuilder diffCellValueRecord = new StringBuilder("");// 比较 cs1 和 cs2 的值 并记录

            for (int i = 1; i < arr1.length -1; i++) {
                String cs1 = arr1[i];
                String cs2 = arr2[i];
                // 判断两个数组当前索引的值是否有多个逗号隔开
                boolean s1B = IntStream.range(0, cs1.length()).filter(ii -> cs1.charAt(ii) == '，' || cs1.charAt(ii) == ',').count() >= 1 ? true:false;
                boolean s2B = IntStream.range(0, cs2.length()).filter(jj -> cs2.charAt(jj) == '，' || cs2.charAt(jj) == ',').count() >= 1 ? true:false;
                // 1、都没有多个逗号隔开的值
                if(!s1B && !s2B && !cs1.equals(cs2)) {
                    // 添加两个文件不同的值到可变字符串中
                    diffCellValueRecord.append("SINGLE1DIFF:" + cs1 + "@@");
                    diffCellValueRecord.append("SINGLE2DIFF:" + cs2 + "@@");
                }
                // 2、有多个逗号隔开的值 当两个表格当前行对应的 单元格有一个  是逗号分隔的
                if(s1B || s2B) {
                    List<String> s1Split = Arrays.asList(cs1.split(","));
                    List<String> s2Split = Arrays.asList(cs2.split(","));
                    // 文件1中当前单元格有  文件2 当前单元格没有
                    for (String s1 : s1Split) {
                        if (!s2Split.contains(s1)) {
                            diffCellValueRecord.append("MULTIPLE1DIFF:" + s1 + "@@");
                        }
                    }
                    // 文件2中当前单元格有  文件1 当前单元格没有
                    for (String s1 : s2Split) {
                        if (!s1Split.contains(s1)) {
                            diffCellValueRecord.append("MULTIPLE2DIFF:" + s1 + "@@");
                        }
                    }
                }
            }

            // 如果有不同 则 合并两个 List 到一行里面
            if(diffCellValueRecord.toString().length() > 0) {
                collectValues.put(Arrays.asList(diffCellValueRecord.toString().split("@@")),Arrays.asList(strings));
            }

        }



        // 创建时间字符串
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日HH时mm分ss秒");
        String formattedTime = now.format(formatter);
        Sheet secondSheet = mergedWorkbook.createSheet("对比结果");

        int rownum = 0;

        // 转换一下数据结构
        Map<String, List<String>> stringListMap = convertMapKey(collectValues);
        filterMap(stringListMap);//去重数据
        Set<String> result = stringListMap.keySet();
        for (String s : result) {
            Row row = secondSheet.createRow(rownum++);// 每次循环创建新行
            // 第一列
            //Cell titleCell = row.createCell(0); // 创建标题单元格
            //titleCell.setCellValue(s);

            // 存在逗号隔开的单元格内容不同  MULTIPLE1DIFFC4055,MULTIPLE2DIFFC4006  提取出数组
            List<String> multiDiffValues = new ArrayList<>();
            if(s.contains("MULTIPLE1DIFF:") || s.contains("MULTIPLE2DIFF:")) {
                String[] split = s.split(",");
                for (String s1 : split) {
                    multiDiffValues.add(s1.replace("MULTIPLE1DIFF:","")
                        .replace("MULTIPLE2DIFF:",""));
                }
            }

            List<String> singleDiffValues = new ArrayList<>();
            if(s.contains("SINGLE1DIFF:") || s.contains("SINGLE2DIFF:")) {
                String[] split = s.split(",");
                for (String s1 : split) {
                    singleDiffValues.add(s1.replace("SINGLE1DIFF:","")
                        .replace("SINGLE2DIFF:",""));
                }
            }

            boolean onlyV = (s.contains("ONLY_EXCEL_ONE") || s.contains("ONLY_EXCEL_TWO")) ? true:false;

            // 获取当前记录值对应的行数据
            List<String> data = stringListMap.get(s);
            for (int i = 0; i < data.size(); i++) { // 修正索引从0开始
                Cell cell = row.createCell(i + 1); // 列号从1开始

                // 给单元格字符串中的字体单独设置颜色
                String currs = data.get(i);
                boolean dotsb = IntStream
                    .range(0, currs.length())
                    .filter(ii -> currs.charAt(ii) == '，' || currs.charAt(ii) == ',')
                    .count() >= 1 ? true:false; // 当前单元格内容是否包含多个逗号
                if (multiDiffValues.size() > 0 && dotsb) { // multiDiffValues 表示有此类值 dotsb 表示当前遍历到的单元格包含逗号
                    String[] values = currs.split(",");
                    XSSFRichTextString richText = new XSSFRichTextString();
                    // 创建黄色背景样式
                    CellStyle multiDiffCellStyle = mergedWorkbook.createCellStyle();
                    multiDiffCellStyle.setBorderTop(BorderStyle.THIN);
                    multiDiffCellStyle.setBorderBottom(BorderStyle.THIN);
                    multiDiffCellStyle.setBorderLeft(BorderStyle.THIN);
                    multiDiffCellStyle.setBorderRight(BorderStyle.THIN);
                    multiDiffCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    multiDiffCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
                    int count = 0;
                    for (String v : values) {
                        if (multiDiffValues.contains(v.trim())) {
                            // 创建红色字体样式
                            Font redFont = ((XSSFWorkbook) mergedWorkbook).createFont();
                            redFont.setColor(IndexedColors.RED.getIndex());
                            richText.append(v, (XSSFFont) redFont);
                        } else {
                            richText.append(v);
                        }
                        count++;
                        if (count < values.length) {
                            richText.append(",");
                        }
                    }
                    cell.setCellValue(richText);
                    cell.setCellStyle(multiDiffCellStyle);
                    count = 0;
                }
                else {
                    CellStyle style = mergedWorkbook.createCellStyle();
                    style.setBorderTop(BorderStyle.THIN);
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderRight(BorderStyle.THIN);
                    cell.setCellValue(data.get(i));
                    cell.setCellStyle(style);
                }

                if (singleDiffValues.size() > 0) {
                    XSSFRichTextString richText = new XSSFRichTextString();
                    CellStyle singleDiffCellStyle = mergedWorkbook.createCellStyle();
                    singleDiffCellStyle.setBorderTop(BorderStyle.THIN);
                    singleDiffCellStyle.setBorderBottom(BorderStyle.THIN);
                    singleDiffCellStyle.setBorderLeft(BorderStyle.THIN);
                    singleDiffCellStyle.setBorderRight(BorderStyle.THIN);
                    if (singleDiffValues.contains(currs.trim())) {
                        Font redFont = ((XSSFWorkbook) mergedWorkbook).createFont();
                        redFont.setColor(IndexedColors.RED.getIndex());
                        richText.append(currs, (XSSFFont) redFont);
                    } else {
                        richText.append(currs);
                    }
                    cell.setCellValue(richText);
                    cell.setCellStyle(singleDiffCellStyle);
                }

                if (onlyV) {
                    XSSFRichTextString richText = new XSSFRichTextString();
                    // 创建黄色背景样式
                    CellStyle onlyVCellStyle = mergedWorkbook.createCellStyle();
                    onlyVCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    onlyVCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                    onlyVCellStyle.setBorderTop(BorderStyle.THIN); // 边框
                    onlyVCellStyle.setBorderBottom(BorderStyle.THIN);// 边框
                    onlyVCellStyle.setBorderLeft(BorderStyle.THIN);// 边框
                    onlyVCellStyle.setBorderRight(BorderStyle.THIN);// 边框
                    CellStyle onlyVCellStyle1 = mergedWorkbook.createCellStyle();
                    onlyVCellStyle1.setBorderTop(BorderStyle.THIN); // 边框
                    onlyVCellStyle1.setBorderBottom(BorderStyle.THIN);// 边框
                    onlyVCellStyle1.setBorderLeft(BorderStyle.THIN);// 边框
                    onlyVCellStyle1.setBorderRight(BorderStyle.THIN);// 边框

                    if (!currs.trim().isEmpty()) {
                        Font redFont = ((XSSFWorkbook) mergedWorkbook).createFont();
                        redFont.setColor(IndexedColors.RED.getIndex());
                        richText.append(currs, (XSSFFont) redFont);
                    } else {
                        richText.append(currs);
                    }
                    cell.setCellValue(richText);
                    if (!currs.trim().isEmpty()) {
                        cell.setCellStyle(onlyVCellStyle);
                    } else {
                        cell.setCellStyle(onlyVCellStyle1);
                    }

                }


            }
        }

        // 转换为字节数组
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        mergedWorkbook.write(bos);
        byte[] excelBytes = bos.toByteArray();

        ResponseDTO responseDTO = new ResponseDTO();
        responseDTO.setCode(200);
        responseDTO.setMsg("文件处理成功");

        // 转换为 InputStream（新增）
        try (ByteArrayInputStream excelInputStream = new ByteArrayInputStream(excelBytes)) {
            String fileUrl = minioConfig.putExcelObject(
                excelInputStream,
                formattedTime + "的对比结果.xlsx",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            );
            responseDTO.setData(fileUrl);
        }
        workbook1.close();
        workbook2.close();
        mergedWorkbook.close();
        bos.close();
        return responseDTO;
    }

    private void copyCellValueTwo(Cell sourceCell, Cell targetCell) {
        switch (sourceCell.getCellType()) {
            case STRING:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(sourceCell)) {
                    targetCell.setCellValue(sourceCell.getDateCellValue());
                } else {
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            default:
                break;
        }
    }

    /**
     * 将 key 中包含SINGLE并且 对应集合相同的 Map元素删除
     * @param map
     */
    public static void filterMap(Map<String, List<String>> map) {
        // 用于存储每个值对应的键列表
        Map<List<String>, List<String>> valueToKeys = new HashMap<>();

        // 遍历原 Map，将值相同的键分组
        for (Map.Entry<String, List<String>> entry : map.entrySet()) {
            List<String> value = entry.getValue();
            String key = entry.getKey();
            valueToKeys.computeIfAbsent(value, k -> new ArrayList<>()).add(key);
        }

        // 遍历分组结果，删除符合条件的元素
        for (Map.Entry<List<String>, List<String>> entry : valueToKeys.entrySet()) {
            List<String> keys = entry.getValue();
            if (keys.size() > 1) {
                for (String key : keys) {
                    if (key.contains("SINGLE")) {
                        map.remove(key);
                    }
                }
            }
        }
    }

    public static String[] getTargetStringArr(String v,String[][] indexTwoValueArr){
        String[] result = null;
        for (int i = 1; i < indexTwoValueArr.length; i++) {
            String[] strings = indexTwoValueArr[i];
            if (strings[1].equals(v) || strings[2].equals(v) || strings[3].equals(v) || strings[4].equals(v)) {
                result = strings;
            }
        }
        return result;
    }
    public static String[] getTargetStringArr(String v1,String v2,String v3,String v4,String[][] indexTwoValueArr){
        String[] result = null;
        for (int i = 0; i < indexTwoValueArr.length; i++) {
            String[] s = indexTwoValueArr[i];
            if (s[1].equals(v1) || s[2].equals(v2) || s[3].equals(v3) || s[4].equals(v4)) {
                result = s;
            }
        }
        return result;
    }


    /**
     * 将 Map<List<String>, List<String>> 的 key 全部转换为字符串 修改数据类型为 Map<String, List<String>>
     * @param originalMap
     * @return
     */
    public static Map<String, List<String>> convertMapKey(Map<List<String>, List<String>> originalMap) {
        Map<String, List<String>> resultMap = new HashMap<>();
        for (Map.Entry<List<String>, List<String>> entry : originalMap.entrySet()) {
            List<String> keyList = entry.getKey();
            StringBuilder keyStringBuilder = new StringBuilder();
            for (int i = 0; i < keyList.size(); i++) {
                keyStringBuilder.append(keyList.get(i));
                if (i < keyList.size() - 1) {
                    keyStringBuilder.append(",");
                }
            }
            String keyString = keyStringBuilder.toString();
            resultMap.put(keyString, entry.getValue());
        }
        return resultMap;
    }

    private static void copySheet(Sheet srcSheet, Sheet destSheet, Workbook destWorkbook) {
        // 复制列宽
        for (int i = 0; i < srcSheet.getRow(0).getLastCellNum(); i++) {
            destSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
        }

        // 逐行复制
        for (int r = 0; r <= srcSheet.getLastRowNum(); r++) {
            Row srcRow = srcSheet.getRow(r);
            if (srcRow == null) {
                continue;
            }

            Row destRow = destSheet.createRow(r);
            copyRow(srcRow, destRow, destWorkbook);
        }
    }

    private static void copyRow(Row srcRow, Row destRow, Workbook destWorkbook) {
        CellStyle newStyle = destWorkbook.createCellStyle();
        newStyle.cloneStyleFrom(srcRow.getCell(0).getCellStyle());

        for (int c = 0; c < srcRow.getLastCellNum(); c++) {
            Cell srcCell = srcRow.getCell(c);
            if (srcCell == null) {
                continue;
            }

            Cell destCell = destRow.createCell(c);
            destCell.setCellStyle(newStyle);

            switch (srcCell.getCellType()) {
                case STRING:
                    destCell.setCellValue(srcCell.getStringCellValue());
                    break;
                case NUMERIC:
                    destCell.setCellValue(srcCell.getNumericCellValue());
                    break;
                // 其他类型处理...
            }
        }
    }

    public static Map<String, List<String>> compareTwoDimensionalArrays(String[][] dataArrayOne, String[][] dataArrayTwo) {
        Map<String, List<String>> resultMap = new HashMap<>();
        int minRowCount = Math.min(dataArrayOne.length, dataArrayTwo.length);
        for (int row = 0; row < minRowCount; row++) {
            String[] rowOne = dataArrayOne[row];
            String[] rowTwo = dataArrayTwo[row];
            int minColCount = Math.min(rowOne.length, rowTwo.length);
            for (int col = 0; col < minColCount; col++) {
                if (!Objects.equals(rowOne[col], rowTwo[col])) {
                    List<String> valueList = new ArrayList<>();
                    valueList.add(rowOne[col]);
                    for (int i = 0; i < 8; i++) {
                        valueList.add("");
                    }
                    valueList.add(rowTwo[col]);
                    resultMap.put("Row " + (row + 1) + " Col " + (col + 1), valueList);
                }
            }
            // 处理列数不同的情况
            if (rowOne.length != rowTwo.length) {
                int maxColCount = Math.max(rowOne.length, rowTwo.length);
                for (int col = minColCount; col < maxColCount; col++) {
                    List<String> valueList = new ArrayList<>();
                    if (col < rowOne.length) {
                        valueList.add(rowOne[col]);
                    } else {
                        valueList.add("");
                    }
                    for (int i = 0; i < 8; i++) {
                        valueList.add("");
                    }
                    if (col < rowTwo.length) {
                        valueList.add(rowTwo[col]);
                    } else {
                        valueList.add("");
                    }
                    resultMap.put("Row " + (row + 1) + " Col " + (col + 1), valueList);
                }
            }
        }
        // 处理行数不同的情况
        if (dataArrayOne.length != dataArrayTwo.length) {
            int maxRowCount = Math.max(dataArrayOne.length, dataArrayTwo.length);
            for (int row = minRowCount; row < maxRowCount; row++) {
                String[] rowData = (row < dataArrayOne.length)? dataArrayOne[row] : dataArrayTwo[row];
                for (int col = 0; col < rowData.length; col++) {
                    List<String> valueList = new ArrayList<>();
                    if (row < dataArrayOne.length) {
                        valueList.add(dataArrayOne[row][col]);
                    } else {
                        valueList.add("");
                    }
                    for (int i = 0; i < 8; i++) {
                        valueList.add("");
                    }
                    if (row < dataArrayTwo.length) {
                        valueList.add(dataArrayTwo[row][col]);
                    } else {
                        valueList.add("");
                    }
                    resultMap.put("Row " + (row + 1) + " Col " + (col + 1), valueList);
                }
            }
        }
        return resultMap;
    }

    public static boolean hasIndexMismatch(List<String> list1, List<String> list2) {
        int minLength = Math.min(list1.size(), list2.size());
        for (int i = 0; i < minLength; i++) {
            if (!Objects.equals(list1.get(i), list2.get(i))) {
                return true;
            }
        }
        // 处理长度不一致的情况（较长列表的剩余元素视为不匹配）
        return list1.size() != list2.size();
    }

    private void processSheet(Sheet sheet1, Sheet sheet2, Workbook mergedWorkbook, CellStyle highlightStyle) {
        Sheet mergedSheet = mergedWorkbook.createSheet(sheet1.getSheetName());
        List<Integer> diffRows = new ArrayList<>();

        int firstColCount = getColumnCount(sheet1);
        int secondColCount = getColumnCount(sheet2);
        int secondStartCol = firstColCount + 8;

        // 处理表头
        copyHeader(sheet1, sheet2, mergedSheet, firstColCount, secondStartCol);

        // 处理数据行
        for (int r = 1; r <= sheet1.getLastRowNum(); r++) {
            Row row1 = sheet1.getRow(r);
            Row row2 = sheet2.getRow(r);
            Row mergedRow = mergedSheet.createRow(r);

            boolean hasDiff = processRow(row1, row2, mergedRow, firstColCount, secondStartCol, highlightStyle);
            if (hasDiff) {
                diffRows.add(r);
            }
        }

        // 添加差异汇总
        addDiffSummary(sheet1, sheet2, mergedSheet, diffRows, firstColCount, secondStartCol, highlightStyle);
    }

    private boolean processRow(Row row1, Row row2, Row mergedRow, int colCount, int secondStartCol, CellStyle highlightStyle) {
        boolean hasDiff = false;
        for (int c = 0; c < colCount; c++) {
            Cell cell1 = getCellSafe(row1, c);
            Cell cell2 = getCellSafe(row2, c);

            // 处理左侧单元格
            Cell mergedCellLeft = mergedRow.createCell(c);
            copyCellValue(cell1, mergedCellLeft);

            // 处理右侧单元格
            Cell mergedCellRight = mergedRow.createCell(secondStartCol + c);
            copyCellValue(cell2, mergedCellRight);

            // 对比值
            if (!getCellValueAsString(cell1).equals(getCellValueAsString(cell2))) {
                mergedCellLeft.setCellStyle(highlightStyle);
                mergedCellRight.setCellStyle(highlightStyle);
                hasDiff = true;
            }
        }
        return hasDiff;
    }

    private void addDiffSummary(Sheet sheet1, Sheet sheet2, Sheet mergedSheet, List<Integer> diffRows,
                                int colCount, int secondStartCol, CellStyle highlightStyle) {
        int startRow = mergedSheet.getLastRowNum() + 5;

        for (int rowNum : diffRows) {
            Row sourceRow1 = sheet1.getRow(rowNum);
            Row sourceRow2 = sheet2.getRow(rowNum);

            // 第一个文件差异行
            Row summaryRow1 = mergedSheet.createRow(startRow++);
            copyDiffRow(sourceRow1, summaryRow1, 0, colCount, highlightStyle);

            // 第二个文件差异行
            Row summaryRow2 = mergedSheet.createRow(startRow++);
            copyDiffRow(sourceRow2, summaryRow2, secondStartCol, colCount, highlightStyle);
        }
    }

    private void copyDiffRow(Row sourceRow, Row targetRow, int startCol, int colCount, CellStyle style) {
        for (int c = 0; c < colCount; c++) {
            Cell sourceCell = getCellSafe(sourceRow, c);
            Cell targetCell = targetRow.createCell(startCol + c);
            copyCellValue(sourceCell, targetCell);
            targetCell.setCellStyle(style);
        }
    }

    private CellStyle createHighlightStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        style.setFont(font);
        return style;
    }

    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN: return String.valueOf(cell.getBooleanCellValue());
            case FORMULA: return cell.getCellFormula();
            default: return "";
        }
    }

    private void copyCellValue(Cell source, Cell target) {
        if (source == null) {
            return;
        }
        switch (source.getCellType()) {
            case STRING: target.setCellValue(source.getStringCellValue()); break;
            case NUMERIC: target.setCellValue(source.getNumericCellValue()); break;
            case BOOLEAN: target.setCellValue(source.getBooleanCellValue()); break;
            case FORMULA: target.setCellFormula(source.getCellFormula()); break;
        }
    }

    private Cell getCellSafe(Row row, int col) {
        return row == null ? null : row.getCell(col, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    }

    private int getColumnCount(Sheet sheet) {
        return sheet.getRow(0) == null ? 0 : sheet.getRow(0).getLastCellNum();
    }

    private void copyHeader(Sheet sheet1, Sheet sheet2, Sheet mergedSheet, int firstColCount, int secondStartCol) {
        Row headerRow = mergedSheet.createRow(0);
        copyHeaderCells(sheet1.getRow(0), headerRow, 0, firstColCount);
        copyHeaderCells(sheet2.getRow(0), headerRow, secondStartCol, firstColCount);
    }

    private void copyHeaderCells(Row sourceRow, Row targetRow, int startCol, int colCount) {
        for (int c = 0; c < colCount; c++) {
            Cell sourceCell = getCellSafe(sourceRow, c);
            Cell targetCell = targetRow.createCell(startCol + c);
            copyCellValue(sourceCell, targetCell);
        }
    }

    /**
     *
     * @param fileFirst  第一个Excel文件
     * @param fileSecond 第二个Excel文件
     * @param formData   添加的额外数据
     * @return
     *  统一结果返回对象  这个是返回统一的响应对象，将文件转换转换为了base64的格式 但是手机端响应下载会提示打开浏览器
     *  但是打开浏览器之后并不会下载文件，电脑端是可以下载的
     * @throws Exception
     */
    @PostMapping(value = "/chkExcelAllCell2", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public ResponseDTO chkExcelAllCell2(
            @RequestParam("excel_first") MultipartFile fileFirst,
            @RequestParam("excel_second") MultipartFile fileSecond,
            @RequestParam(required = false) Map<String, String> formData) throws Exception {

        // 读取两个Excel文件
        Workbook workbook1 = WorkbookFactory.create(fileFirst.getInputStream());
        Workbook workbook2 = WorkbookFactory.create(fileSecond.getInputStream());


        // 获取总行数（包括空行，即使整行无数据也会被计入）
        Sheet sheetAtOne = workbook1.getSheetAt(0);
        int rowCount = sheetAtOne.getLastRowNum() + 1; // getLastRowNum()返回最后一行索引，从0开始，+1得到总行数
        String[][] dataArrayOne = new String[rowCount][];
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            Row row = sheetAtOne.getRow(rowNum);
            int lastCellNum = (row != null) ? row.getLastCellNum() : -1; // 获取该行最后一个单元格索引（无单元格时为-1）
            int columnCount = (lastCellNum >= 0) ? lastCellNum + 1 : 0; // 列数=最后索引+1，无单元格时为0
            String[] cellArray = new String[columnCount];
            for (int cellNum = 0; cellNum < columnCount; cellNum++) {
                Cell cell = (row != null) ? row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK) : null;
                String cellValue = "";
                if (cell != null) {
                    cell.setCellType(CellType.STRING); // 统一转换为字符串类型
                    cellValue = cell.getStringCellValue();
                }
                cellArray[cellNum] = cellValue;
            }
            dataArrayOne[rowNum] = cellArray;
        }

        Sheet sheetAtTwo = workbook2.getSheetAt(0);
        int rowCount1 = sheetAtTwo.getLastRowNum() + 1; // getLastRowNum()返回最后一行索引，从0开始，+1得到总行数
        String[][] dataArrayTwo = new String[rowCount1][];
        for (int rowNum = 0; rowNum < rowCount1; rowNum++) {
            Row row = sheetAtTwo.getRow(rowNum);
            int lastCellNum = (row != null) ? row.getLastCellNum() : -1; // 获取该行最后一个单元格索引（无单元格时为-1）
            int columnCount = (lastCellNum >= 0) ? lastCellNum + 1 : 0; // 列数=最后索引+1，无单元格时为0
            String[] cellArray = new String[columnCount];
            for (int cellNum = 0; cellNum < columnCount; cellNum++) {
                Cell cell = (row != null) ? row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK) : null;
                String cellValue = "";
                if (cell != null) {
                    cell.setCellType(CellType.STRING); // 统一转换为字符串类型
                    cellValue = cell.getStringCellValue();
                }
                cellArray[cellNum] = cellValue;
            }
            dataArrayTwo[rowNum] = cellArray;
        }


        Map<String,List<String>> diffMapList = new HashMap<>();

        String[] dataZeroOne = dataArrayOne[0];
        String[] dataZeroTwo = dataArrayTwo[0];
        List<String> lineOne = new ArrayList<>();
        for (String s : dataZeroOne) { lineOne.add(s); }
        for (int i = 0; i < 8; i++) { lineOne.add(""); }
        for (String s : dataZeroTwo) { lineOne.add(s); }
        diffMapList.put("第0行\t@@@\t", lineOne);

        for (int i = 0; i < dataArrayOne.length; i++) {
            boolean isDiff = false;
            List<String> diffRowValue = new ArrayList<>();
            for (int j = 0; j < dataArrayOne[i].length; j++) {
                if(!dataArrayOne[i][j].equals(dataArrayTwo[i][j])) {
                    isDiff = true;

                    diffRowValue.add(dataArrayOne[i][j]);
                    diffRowValue.add(dataArrayTwo[i][j]);
                } else {
                    isDiff = false;
                }
                if (isDiff) {
                    List<String> result = new ArrayList<>();
                    for (String s : dataArrayOne[i]) {
                        result.add(s);
                    }
                    for (int k = 0; k < 8; k++) {
                        result.add("");
                    }
                    for (String s : dataArrayTwo[i]) {
                        result.add(s);
                    }
                    diffMapList.put("第" + i + "行\t@@@\t" + diffRowValue.toString(), result);
                    isDiff = false;
                }
            }
        }

        // 创建合并后的工作簿  创建新Workbook（使用XSSF处理.xlsx格式）
        Workbook mergedWorkbook = new XSSFWorkbook();
        // 1. 处理第一个Workbook的Sheet
        Sheet srcSheet1 = workbook1.getSheetAt(0);
        Sheet destSheet1 = mergedWorkbook.createSheet(fileFirst.getOriginalFilename().replace(".xlsx",""));
        copySheet(srcSheet1, destSheet1, mergedWorkbook);
        // 2. 处理第二个Workbook的Sheet
        Sheet srcSheet2 = workbook2.getSheetAt(0);
        Sheet destSheet2 = mergedWorkbook.createSheet(fileSecond.getOriginalFilename().replace(".xlsx",""));
        copySheet(srcSheet2, destSheet2, mergedWorkbook);


        // 创建第二个Sheet（索引从0开始，第二个Sheet索引为1）
        Sheet secondSheet = mergedWorkbook.createSheet("对比结果");
        int rownum = 0;
        Set<String> stringSet = diffMapList.keySet();
        for (String s : stringSet) {
            Row row = secondSheet.createRow(rownum++);// 每次循环创建新行
            // 创建标题单元格
//            Cell titleCell = row.createCell(0);
//            titleCell.setCellValue(s);

            List<String> data = diffMapList.get(s);
            for (int i = 0; i < data.size(); i++) { // 修正索引从0开始
                Cell cell = row.createCell(i + 1); // 列号从1开始
                // 设置高亮样式逻辑
                if (s.contains(data.get(i))) {
                    CellStyle combinedStyle = createHighlightStyle(mergedWorkbook);
                    cell.setCellStyle(combinedStyle);
                }
                cell.setCellValue(data.get(i));
            }
        }

        // 转换为字节数组
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        mergedWorkbook.write(bos);
        byte[] excelBytes = bos.toByteArray();
        bos.close();

        // 构建响应
        ResponseDTO responseDTO = new ResponseDTO();
        responseDTO.setCode(200);
        responseDTO.setMsg("文件处理成功");
        responseDTO.setFileName("对比结果.xlsx");
        responseDTO.setFileType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        responseDTO.setData(Base64.getEncoder().encodeToString(excelBytes));

        workbook1.close();
        workbook2.close();
        mergedWorkbook.close();

        return responseDTO;
    }

    /**
     *
     * @param fileFirst  第一个Excel文件
     * @param fileSecond 第二个Excel文件
     * @param formData   添加的额外数据
     * @return           统一结果返回对象
     * @throws Exception
     */
    @PostMapping(value = "/chkExcelAllCell1", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public ResponseDTO chkExcelAllCell1(
            @RequestParam("excel_first") MultipartFile fileFirst,
            @RequestParam("excel_second") MultipartFile fileSecond,
            @RequestParam(required = false) Map<String, String> formData) throws Exception {

        // 读取两个Excel文件
        Workbook workbook1 = WorkbookFactory.create(fileFirst.getInputStream());
        Workbook workbook2 = WorkbookFactory.create(fileSecond.getInputStream());



        // 创建合并后的工作簿
        Workbook mergedWorkbook = new XSSFWorkbook();

        // 创建高亮样式
        CellStyle highlightStyle = createHighlightStyle(mergedWorkbook);

        // 处理每个sheet
//        for (int sheetIndex = 0; sheetIndex < workbook1.getNumberOfSheets(); sheetIndex++) {
//            processSheet(
//                    workbook1.getSheetAt(sheetIndex),
//                    workbook2.getSheetAt(sheetIndex),
//                    mergedWorkbook,
//                    highlightStyle
//            );
//        }

        processSheet(
                workbook1.getSheetAt(0),
                workbook2.getSheetAt(0),
                mergedWorkbook,
                highlightStyle
        );

        System.out.println(123);

        Sheet sheetAtOne = mergedWorkbook.getSheetAt(0);

        // 获取总行数（包括空行，即使整行无数据也会被计入）
        int rowCount = sheetAtOne.getLastRowNum() + 1; // getLastRowNum()返回最后一行索引，从0开始，+1得到总行数

        String[][] dataArray = new String[rowCount][];

        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            Row row = sheetAtOne.getRow(rowNum);
            int lastCellNum = (row != null) ? row.getLastCellNum() : -1; // 获取该行最后一个单元格索引（无单元格时为-1）
            int columnCount = (lastCellNum >= 0) ? lastCellNum + 1 : 0; // 列数=最后索引+1，无单元格时为0

            String[] cellArray = new String[columnCount];

            for (int cellNum = 0; cellNum < columnCount; cellNum++) {
                Cell cell = (row != null) ? row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK) : null;
                String cellValue = "";
                if (cell != null) {
                    cell.setCellType(CellType.STRING); // 统一转换为字符串类型
                    cellValue = cell.getStringCellValue();
                }
                cellArray[cellNum] = cellValue;
            }

            dataArray[rowNum] = cellArray;
        }

        // 打印验证结果（包含空行和空单元格）
//        System.out.println("包含所有行和完整列的二维数组：");
//        for (int i = 0; i < dataArray.length; i++) {
//            System.out.print("第 " + (i + 1) + " 行: ");
//            for (String value : dataArray[i]) {
//                System.out.print((value.isEmpty() ? "[空]" : value) + " | ");
//            }
//            System.out.println();
//        }


        Map<String,List<String>> map = new HashMap<>();
        for (int i = 0; i < dataArray.length; i++) {

            // 找到表格时机应该有多少列
            int colNum = 0;
            int count = 0;
            int index = 0;
            for (int j = dataArray[i].length -1; j >= 0 ; j--) {
                if (dataArray[i][j].equals("")){
                    count++;
                } else {
                    count = 0;
                    index = j;
                }
                if (count == 8){
                    colNum = j;
                }
            }
            // 通过上面找到的列取值进行比较  若比较出不一样的数据则记录整行的值
            List<String> left = new ArrayList<>();
            List<String> right = new ArrayList<>();
            for (int j = 0; j < colNum; j++) {
                left.add(dataArray[i][j].toString());
            }
            for (int j = colNum + 8; j < dataArray[i].length -1; j++) {
                right.add(dataArray[i][j]);
            }
            boolean b = hasIndexMismatch(left, right);
            if (b) {
                map.put("第" + (i + 1) + "行", Arrays.asList(dataArray[i]));
            }

            for (String value : dataArray[i]) {
                System.out.print((value.isEmpty() ? "[空]" : value) + " | ");
            }

        }











        // 创建第二个Sheet（索引从0开始，第二个Sheet索引为1）
        Sheet secondSheet = mergedWorkbook.createSheet("对比结果");

        // 创建字体样式（红色文本）
        Font redFont = mergedWorkbook.createFont();
        redFont.setColor(IndexedColors.RED.getIndex());

        // 创建背景颜色样式（黄色背景）
        CellStyle yellowBackgroundStyle = mergedWorkbook.createCellStyle();
        yellowBackgroundStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowBackgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 创建同时包含文本颜色和背景颜色的样式
        CellStyle combinedStyle = mergedWorkbook.createCellStyle();
        combinedStyle.setFont(redFont);
        combinedStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        combinedStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 写入数据到第二行（行索引从0开始，假设第一行是标题行，第二行开始写数据）
        Row dataRow = secondSheet.createRow(0); // 示例写入第一行数据

        // 创建单元格并设置内容和样式
        Cell textCell = dataRow.createCell(0);
        textCell.setCellValue("带样式的文本");
        textCell.setCellStyle(combinedStyle); // 应用组合样式

        // 创建纯背景颜色的单元格（黑色文本+黄色背景）
        Cell backgroundCell = dataRow.createCell(1);
        backgroundCell.setCellValue("黄色背景");
        backgroundCell.setCellStyle(yellowBackgroundStyle); // 应用背景样式



        CellStyle a = mergedWorkbook.createCellStyle();
        a.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        a.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 创建纯文本颜色的单元格（红色文本+默认背景）
        Cell fontCell = dataRow.createCell(2);
        fontCell.setCellValue("红色文本");
        fontCell.setCellStyle(a); // 单独应用字体样式

        // 调整列宽（可选）
        secondSheet.autoSizeColumn(0);
        secondSheet.autoSizeColumn(1);
        secondSheet.autoSizeColumn(2);


        // 转换为字节数组
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        mergedWorkbook.write(bos);
        byte[] excelBytes = bos.toByteArray();
        bos.close();

        // 构建响应
        ResponseDTO responseDTO = new ResponseDTO();
        responseDTO.setCode(200);
        responseDTO.setMsg("文件处理成功");
        responseDTO.setData(Base64.getEncoder().encodeToString(excelBytes));

        workbook1.close();
        workbook2.close();
        mergedWorkbook.close();

        return responseDTO;
    }

}
